package dao.mysql;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.UserDTO;
import dao.DAOException;
import dao.UserDAO;
import dao.mysql.util.MySQLUtility;


public class UserDAOImpl implements UserDAO {

	private Connection conn;

	public UserDAOImpl()
	{
		conn = null;
	}

	public UserDTO createValueObject() {
		return new UserDTO();
	}
	
	@Override
	public UserDTO getUser(String username) throws DAOException, SQLException {

		UserDTO valueObject = createValueObject();
		valueObject.setUsername(username);

		if (valueObject.getUsername() == null) {
			throw new DAOException("Can not select without Primary-Key!");
		}

		String sql = "SELECT u.* ,m.Name AS ManagerName FROM User AS m, User AS u WHERE u.Id = ? and u.Manager = m.Id "; 
		PreparedStatement stmt = null;
		
		openConnection();
		try {
			stmt = conn.prepareStatement(sql);
			try{
				stmt.setString(1, valueObject.getUsername()); 
				singleQuery(stmt, valueObject);
				conn.commit();
			} finally {
				stmt.close();
			}
		}finally {
			closeConnection(conn);
		}

		return valueObject;
	}
	
	@Override
	public UserDTO[] getAllUsers() throws DAOException, SQLException {

		String sql = "SELECT u.* , m.Name AS ManagerName FROM User AS m, User as u WHERE u.Manager = m.Id ORDER BY Name ASC";
		List<UserDTO> searchResults = new ArrayList<UserDTO>();
		PreparedStatement stmt = null;
		
		openConnection();
		try{
			stmt = conn.prepareStatement(sql);
			try{
				searchResults = listQuery(stmt);
				conn.commit();
			}finally{
				stmt.close();
			}
		}finally{
			closeConnection(conn);
		}
		
		int size = searchResults.toArray().length;
		UserDTO[] users = new UserDTO[size];
		for(int i =0; i < size; i++)
		{
			users[i] = searchResults.get(i);
		}

		return users;
	}

	@Override
	public void insertUser(UserDTO valueObject) throws SQLException {

		String sql = "";
		PreparedStatement stmt = null;

		openConnection();
		try {
			sql = "INSERT INTO User (Email, Name, Type, Password, Manager, Status, Id) VALUES (?, ?, ?, ?, ?, ?, ?) ";
			stmt = conn.prepareStatement(sql);
			try{
				
				stmt.setString(1, valueObject.getEmail()); 
				stmt.setString(2, valueObject.getName()); 
				stmt.setString(3, valueObject.getType()); 
				stmt.setString(4, valueObject.getPassword()); 
				stmt.setString(5, valueObject.getManager()); 
				stmt.setInt(6, valueObject.getStatus()); 
				stmt.setString(7, valueObject.getUsername());

				int rowcount = databaseUpdate(stmt);
				if (rowcount != 1) {
					throw new SQLException("Primary Key Error when updating DB!");
				}
			} finally {
				stmt.close();
			}
		}finally {
			closeConnection(conn);
		}
	}

	@Override
	public void updateUser(UserDTO valueObject) throws DAOException, SQLException {

		String sql = "UPDATE User SET Name = ?, Type = ?, Password = ?, Manager = ?, Status = ?, Email = ? WHERE (Id = ? ) ";
		PreparedStatement stmt = null;

		openConnection();
		try {
			stmt = conn.prepareStatement(sql);
			try{
				stmt.setString(1, valueObject.getName()); 
				stmt.setString(2, valueObject.getType()); 
				stmt.setString(3, valueObject.getPassword()); 
				stmt.setString(4, valueObject.getManager()); 
				stmt.setInt(5, valueObject.getStatus()); 
				stmt.setString(6, valueObject.getEmail()); 
				stmt.setString(7, valueObject.getUsername()); 

				int rowcount = databaseUpdate(stmt);
				if (rowcount == 0) {
					throw new DAOException("Object could not be saved! (PrimaryKey not found)");
				}
				if (rowcount > 1) {
					throw new SQLException("PrimaryKey Error when updating DB! (Many objects were affected!)");
				}
			} finally {
				stmt.close();
			}
		}finally {
			closeConnection(conn);
		}
	}

	@Override
	public void deleteUser(String username) throws DAOException, SQLException {
		
		String sql = "UPDATE User SET Status = 0 WHERE (Id = ?) ";
		PreparedStatement stmt = null;
		
		openConnection();
		try {
			stmt = conn.prepareStatement(sql);
			try{
				stmt.setString(1,username); 
				int rowcount = databaseUpdate(stmt);
				if (rowcount == 0) {
					throw new DAOException("Object could not be saved! (PrimaryKey not found)");
				}
				if (rowcount > 1) {
					throw new SQLException("PrimaryKey Error when updating DB! (Many objects were affected!)");
				}
			} finally {
				stmt.close();
			}
		}finally {
			closeConnection(conn);
		}		
	}

	private int databaseUpdate(PreparedStatement stmt) throws SQLException {

		int result = stmt.executeUpdate();
		conn.commit();
		return result;
	}

	private void singleQuery(PreparedStatement stmt, UserDTO valueObject) throws DAOException, SQLException {

		ResultSet result = stmt.executeQuery();
		try {	
			if (result.next()) {
				valueObject.setUsername(result.getString("Id"));
				valueObject.setEmail(result.getString("Email")); 
				valueObject.setName(result.getString("Name")); 
				valueObject.setType(result.getString("Type")); 
				valueObject.setPassword(result.getString("Password")); 
				valueObject.setManager(result.getString("Manager")); 
				valueObject.setStatus(result.getInt("Status")); 
				valueObject.setManagerName(result.getString("ManagerName"));
			} else {
				throw new DAOException("UserDTO Object Not Found!");
			}
		} finally {
			result.close();
		}
	}

	private List<UserDTO> listQuery(PreparedStatement stmt) throws SQLException {

		ArrayList<UserDTO> searchResults = new ArrayList<UserDTO>();
		ResultSet result = stmt.executeQuery();
		
		try {
			while (result.next()) {
				UserDTO temp = createValueObject();

				temp.setUsername(result.getString("Id"));
				temp.setEmail(result.getString("Email"));
				temp.setName(result.getString("Name")); 
				temp.setType(result.getString("Type")); 
				temp.setPassword(result.getString("Password"));  
				temp.setManager(result.getString("Manager")); 
				temp.setStatus(result.getInt("Status")); 
				temp.setManagerName(result.getString("ManagerName"));
				searchResults.add(temp);
			}

		} finally {
			result.close();
		}

		return searchResults;
	}

	private void openConnection(){
		try {
			conn = MySQLUtility.getConnection();
			conn.setAutoCommit(false);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void closeConnection(Connection conn){
		try{
			conn.rollback();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public UserDTO[] getAllManagers() throws DAOException, SQLException {
		String sql = "SELECT u.* , m.Name AS ManagerName FROM User AS m, User as u WHERE u.Manager = m.Id AND u.Type='Manager' ORDER BY Name ASC";
		List<UserDTO> searchResults = new ArrayList<UserDTO>();
		PreparedStatement stmt = null;
		
		openConnection();
		try{
			stmt = conn.prepareStatement(sql);
			try{
				searchResults = listQuery(stmt);
				conn.commit();
			}finally{
				stmt.close();
			}
		}finally{
			closeConnection(conn);
		}
		
		int size = searchResults.toArray().length;
		UserDTO[] managers = new UserDTO[size];
		for(int i =0; i < size; i++)
		{
			managers[i] = searchResults.get(i);
		}

		return managers;
	}

}
